"""本文件将从淘宝购买的历史行情数据，从CSV文件中读取，然后写入SQLite.

比如sr709.csv的内容为:
市场代码,合约代码,时间,开,高,低,收,成交量,成交额,持仓量
ZC,SR709,2017-01-03 09:05:00,6837.0000,6848.0000,6765.0000,6812.0000,
    1686.0000,11486718.0000,62280
...

将文件写入5分钟表后，再生成30分钟和60分钟的数据。
"""

import datetime
import os

import sqlite3

import numpy as np

import pandas as pd


data_dir = "d:\\futures_data\\"
conn = sqlite3.connect("data.sqlite")
file_list = os.listdir(data_dir)
for filename in file_list:
    contract, ext = filename.split(".")
    if ext.upper() == "CSV":
        fullfilename = os.path.join(data_dir, filename)
        print(fullfilename)
        df = pd.read_csv(fullfilename, encoding="gbk")
        # 列名为中文，更改为英文
        df.columns = ["code", "contract", "datetime", "open", "high",
                      "low", "close", "cjl", "amount", "volume"]
        # 只取其中7列
        sql_df = df[["datetime", "open", "high", "low", "close",
                     "amount", "volume"]]
        tbl_name = "%s_5m" % contract.upper()
        sql_df.to_sql(tbl_name, conn, if_exists='replace', index=True)
        data_list = np.array(sql_df).tolist()
        table_30m_name = "%s_30m" % contract.upper()
        table_60m_name = "%s_60m" % contract.upper()
        print("重建 %s, %s 两个周期的历史记录表。" % (table_30m_name, table_60m_name))
        conn.execute("DROP TABLE IF EXISTS %s" % table_30m_name)
        conn.execute("""CREATE TABLE %s
            (id INTEGER PRIMARY KEY AUTOINCREMENT,
            DATETIME TEXT NOT NULL,
            OPEN FLOAT NOT NULL,
            HIGH FLOAT NOT NULL,
            LOW FLOAT NOT NULL,
            CLOSE FLOAT NOT NULL,
            AMOUNT LONG NOT NULL,
            VOLUME INT NOT NULL);""" % table_30m_name)
        conn.execute("DROP TABLE IF EXISTS %s" % table_60m_name)
        conn.execute("""CREATE TABLE %s
            (id INTEGER PRIMARY KEY AUTOINCREMENT,
            DATETIME TEXT NOT NULL,
            OPEN FLOAT NOT NULL,
            HIGH FLOAT NOT NULL,
            LOW FLOAT NOT NULL,
            CLOSE FLOAT NOT NULL,
            AMOUNT LONG NOT NULL,
            VOLUME INT NOT NULL);""" % table_60m_name)
        hprice_30 = []
        hprice_60 = []
        l_price_30 = []
        l_price_60 = []
        vol_30 = []
        vol_60 = []
        intraday_list = []
        oprice_30 = 0
        oprice_60 = 0
        is_day_finished = False
        for i in range(len(data_list) - 1):
            data1 = data_list[i]
            dt_str1, oprice, hprice, lprice, cprice, amount, vol = data1
            dt1 = datetime.datetime.strptime(dt_str1, "%Y-%m-%d %H:%M:%S")
            date2, time2 = data_list[i + 1][0].split(" ")
            # 将记录加入列表，等到出现收盘记录，再进行整理和合并
            intraday_list.append(data1)
            if (dt1.hour == 15 and dt1.minute == 0):
                is_day_finished = True
            if i == len(data_list) - 2:
                intraday_list.append(data_list[i + 1])
                is_day_finished = True
            if is_day_finished:
                # 将夜盘更改为后一交易日的日期
                for j in range(len(intraday_list)):
                    (dt_strj, oprice, hprice, lprice, cprice,
                     amount, vol) = intraday_list[j]
                    dtj = datetime.datetime.strptime(dt_strj, "%Y-%m-%d %H:%M:%S")
                    if dtj.hour > 15:
                        datej, timej = intraday_list[j][0].split(" ")
                        new_datestr = "%s %s" % (date2, timej)
                        intraday_list[j][0] = new_datestr
                    else:
                        new_datestr = intraday_list[j][0]
                    if len(hprice_30) == 0:
                        oprice_30 = oprice
                    if len(hprice_60) == 0:
                        oprice_60 = oprice
                    hprice_30.append(hprice)
                    hprice_60.append(hprice)
                    l_price_30.append(lprice)
                    l_price_60.append(lprice)
                    vol_30.append(vol)
                    vol_60.append(vol)

                    # 够6条就合成一条30分钟记录
                    if len(hprice_30) == 6 or j == len(intraday_list) - 1:
                        r_hprice = max(hprice_30)
                        r_lprice = min(l_price_30)
                        r_cprice = cprice
                        r_vol = sum(vol_30)
                        r_amount = amount
                        sql_str = ("INSERT INTO %s (DATETIME, OPEN, HIGH, "
                                   "LOW, CLOSE, AMOUNT, VOLUME) values "
                                   "(%r, %.2f, %.2f, %.2f, %.2f, %d, %d"
                                   ")" % (table_30m_name, new_datestr,
                                          oprice_30, r_hprice, r_lprice,
                                          r_cprice, r_amount, r_vol))
                        try:
                            conn.execute(sql_str)
                        except sqlite3.OperationalError as error:
                            print("插入表 %s 出现错误，错误信息是：%s" % (table_30m_name, error))
                        finally:
                            hprice_30 = []
                            l_price_30 = []
                            vol_30 = []
                    if len(hprice_60) == 12 or j == len(intraday_list) - 1:
                        r_hprice = max(hprice_60)
                        r_lprice = min(l_price_60)
                        r_cprice = cprice
                        r_vol = sum(vol_60)
                        r_amount = amount
                        sql_str = ("INSERT INTO %s (DATETIME, OPEN, HIGH, LOW, CLOSE, "
                                   "AMOUNT, VOLUME) values (%r, %.2f, %.2f, %.2f, %.2f, "
                                   "%d, %d)" % (table_60m_name, new_datestr, oprice_60,
                                                r_hprice, r_lprice, r_cprice,
                                                r_amount, r_vol))
                        try:
                            conn.execute(sql_str)
                        except sqlite3.OperationalError as error:
                            print("插入表 %s 出现错误，错误信息是：%s" % (table_60m_name, error))
                        finally:
                            hprice_60 = []
                            l_price_60 = []
                            vol_60 = []
                            # conn.commit()
                is_day_finished = False
                intraday_list = []

conn.commit()
conn.close()


